package com.wrench.elevator.tida.util;

import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.wrench.elevator.tida.domain.FlowDetails;
import com.wrench.elevator.tida.domain.User;
import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

import javax.servlet.http.HttpServletResponse;

public class ExcelUtil {
	public static Map[] analyseExcel(InputStream in, String[] keys) {
		
		Workbook book = null;
		try {
			book = Workbook.getWorkbook(in);
		} catch (BiffException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Sheet sheet = book.getSheet(0);
		Map map = null;
		//System.out.println();
		Map[] values = new Map[sheet.getRows()-1];
		for (int j = 1; j < sheet.getRows(); j++) {
			map = new HashMap();
			Cell[] cells = sheet.getRow(j);
			for (int i = 0; i < keys.length; i++) {
				map.put(keys[i], cells[i].getContents().trim());
			}
			values[j-1]=map;
		}
		return values;
	}
	
	public static void main(String[] args) throws FileNotFoundException {
		FileInputStream is=new FileInputStream("d://模板.xls");
		String[] keys={"zhuang_name","danyuan_name"};
		Map[] values = analyseExcel(is, keys);
		for(Map m:values) System.out.println(m);
	}

	public static void outputExcelFlowList(List<FlowDetails> flows, HttpServletResponse response) {
		WritableWorkbook book = null;
		OutputStream os = null;
		response.reset();
		response.setContentType("application/msexcel");
		String fileName = null;
		try {
			fileName = new String("data".getBytes(), "ISO-8859-1");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
		try {
			os = response.getOutputStream();
			book = Workbook.createWorkbook(os);
			WritableSheet sheet = book.createSheet("data", 0);
			WritableCellFormat cellFormat = new WritableCellFormat();
			cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
			cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			cellFormat.setWrap(true);
			cellFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
			CellView cellView = new CellView();
			cellView.setAutosize(true); // 设置自动大小
			for (int i = 0; i <= 6; i++) {
				sheet.setColumnView(i, cellView);
			}

			sheet.addCell(new Label(0, 1, "编号", cellFormat));
			sheet.addCell(new Label(1, 1, "小区", cellFormat));
			sheet.addCell(new Label(2, 1, "单元", cellFormat));
			sheet.addCell(new Label(3, 1, "时间", cellFormat));
			sheet.addCell(new Label(4, 1, "消费类型", cellFormat));
			sheet.addCell(new Label(5, 1, "消费金额", cellFormat));
			sheet.addCell(new Label(6, 1, "账户余额", cellFormat));
			for (int i = 0; i < flows.size(); i++) {
				sheet.addCell(new Label(0, i + 2, flows.get(i).getFlow_id(), cellFormat));
				sheet.addCell(new Label(1, i + 2, flows.get(i).getBuilding_name(), cellFormat));
				sheet.addCell(new Label(2, i + 2, flows.get(i).getZhuang_danyuan(), cellFormat));
				sheet.addCell(new Label(3, i + 2, flows.get(i).getConsume_time(), cellFormat));
				sheet.addCell(new Label(4, i + 2, flows.get(i).getConsume_type(), cellFormat));
				sheet.addCell(new Label(5, i + 2, flows.get(i).getConsume_money(), cellFormat));
				sheet.addCell(new Label(6, i + 2, flows.get(i).getBalance(), cellFormat));
			}

			book.write();
			book.close();

		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}




	public static void outputExcelUserList(List<User> users, HttpServletResponse response) {
		WritableWorkbook book = null;
		OutputStream os = null;
		response.reset();
		response.setContentType("application/msexcel");
		String fileName = null;
		try {
			fileName = new String("data".getBytes(), "ISO-8859-1");
		} catch (UnsupportedEncodingException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
		try {
			os = response.getOutputStream();
			book = Workbook.createWorkbook(os);
			WritableSheet sheet = book.createSheet("data", 0);
			WritableCellFormat cellFormat = new WritableCellFormat();
			cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
			cellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			cellFormat.setWrap(true);
			cellFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
			CellView cellView = new CellView();
			cellView.setAutosize(true); // 设置自动大小
			for (int i = 0; i <= 3; i++) {
				sheet.setColumnView(i, cellView);
			}

			sheet.addCell(new Label(0, 1, "楼层", cellFormat));
			sheet.addCell(new Label(1, 1, "门牌号", cellFormat));
			sheet.addCell(new Label(2, 1, "用户账号", cellFormat));
			sheet.addCell(new Label(3, 1, "账户余额", cellFormat));
			for (int i = 0; i < users.size(); i++) {
				sheet.addCell(new Label(0, i + 2, users.get(i).getLoucen(), cellFormat));
				sheet.addCell(new Label(1, i + 2, users.get(i).getMenpaihao(), cellFormat));
				sheet.addCell(new Label(2, i + 2, users.get(i).getUser_tel(), cellFormat));
				sheet.addCell(new Label(3, i + 2, String.valueOf(users.get(i).getBalance()), cellFormat));

			}

			book.write();
			book.close();

		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	
}
